﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using RN.ERP.Entity.QD;
using System.Data.Entity;
using RN.ERP.Entity.ZBDLRY;
using RN.ERP.DAL.Context;
using System.Data;
using System.Reflection;
using System.Collections;
using RN.ERP.Entity;
using RN.ERP.DAL.Common;

namespace RN.ERP.ZbdlryDAL
{
    public class PersonDALWh
    {
        private ZBDL_Context zbdlcontext = new ZBDL_Context();


        /// <summary>
        /// 获取企业下的管理人员信息。
        /// </summary>
        /// <param name="corpUserGuid">企业编号。</param>
        /// <returns></returns>
        public List<tb_Emp_Employee> GetPersonManagers(string corpUserGuid, string condition)
        {
            var list = new List<tb_Emp_Employee>();

            string sql = "SELECT * FROM tb_Emp_Employee where typeflag>-1 and flowcode like '1%' and CorpUserGuid='" + corpUserGuid + "' " +
            (string.IsNullOrEmpty(condition) ? string.Empty : condition);
            list = zbdlcontext.Database.SqlQuery<tb_Emp_Employee>(sql).ToList();

            return list;

        }

        /// <summary>
        /// 获取企业下的普通工人信息。
        /// </summary>
        /// <param name="corpUserGuid">企业编号。</param>
        /// <returns></returns>
        public List<tb_Emp_Manager_Zbdl> GetPersonEmployee(string corpUserGuid, string condition)
        {
            var list = new List<tb_Emp_Manager_Zbdl>();

            string sql = "SELECT * FROM tb_Emp_Manager where CorpGuid='" + corpUserGuid + "' " +
            (string.IsNullOrEmpty(condition) ? string.Empty : condition);
            list = zbdlcontext.Database.SqlQuery<tb_Emp_Manager_Zbdl>(sql).ToList();
            return list;
        }

        /// <summary>
        /// 获取证书ID对应人员信息。
        /// </summary>
        /// <param name="qualityId">证书ID。</param>
        /// <returns></returns>
        public List<tb_Emp_Manager_Zbdl> GetPersonEmployeeByQualityId(string qualityId)
        {
            var list = new List<tb_Emp_Manager_Zbdl>();

            string sql = "SELECT * FROM tb_Emp_Manager where exists (select 1 from tb_emp_titledetails where id=" + qualityId + " and empguid=tb_emp_manager.empguid)";
            list = zbdlcontext.Database.SqlQuery<tb_Emp_Manager_Zbdl>(sql).ToList();
            return list;
        }
        /// <summary>
        /// 获取B证ID对应人员信息。
        /// </summary>
        /// <param name="qualityBId"B郑ID编号。</param>
        /// <returns></returns>
        public List<tb_Emp_Manager_Zbdl> GetPersonEmployeeByQualityBId(string qualityBId)
        {
            var list = new List<tb_Emp_Manager_Zbdl>();
            int id = 0;
            Guid guid = new Guid();
            string sql = "";
            if (int.TryParse(qualityBId, out id))
            {
                sql = "SELECT * FROM tb_Emp_Manager where exists (select 1 from tb_emp_titledetailsExt where id=" + qualityBId + " and empguid=tb_emp_manager.empguid)";

            }
            else if (Guid.TryParse(qualityBId, out guid))
            {
                sql = "SELECT * FROM tb_Emp_Manager where exists (select 1 from tb_emp_titledetailsExt where empguid='" + qualityBId + "' and empguid=tb_emp_manager.empguid)";
            }
            list = zbdlcontext.Database.SqlQuery<tb_Emp_Manager_Zbdl>(sql).ToList();
            return list;
        }
        public Tb_Emp_TitleDetailsExt GetPersonBQualityById(string personId)
        {
            string strSql = "SELECT * FROM dbo.tb_Emp_TitleDetailsExt WHERE EXISTS( SELECT 1 FROM dbo.tb_Emp_Manager WHERE ID=@id AND EmpGuid=tb_Emp_TitleDetailsExt.EmpGuid)";
            IList<Tb_Emp_TitleDetailsExt> b = zbdlcontext.Database.SqlQuery<Tb_Emp_TitleDetailsExt>(strSql, new SqlParameter("@id", personId)).ToList();
            if (b.Count > 0)
            {
                return b[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 人员综合统计
        /// </summary>
        /// <param name="flowCode"></param>
        /// <param name="areaCode"></param>
        /// <returns></returns>
        public List<StatisticsModel> GetPersonEmpStatistic(string flowCode, string areaCode)
        {
            string sql = "SELECT   COUNT(DISTINCT m.EmpGuid) AS StatisticsCount, m.ClassId, m.AreaCode FROM  V_EF_Manager AS m INNER JOIN "
                    + " a_AreaSubInfo AS a ON m.AreaCode = a.SortCode WHERE   (m.IsDel = 0 OR m.IsDel IS NULL) AND (m.FlowIsDel = 0 OR	m.FlowIsDel IS NULL) AND (m.RealFlowCode LIKE '%019%')"
           + " AND (m.TypeFlag LIKE '%-1%') AND (m.FlowAreaCode ='" + areaCode + "')";
            if (!string.IsNullOrEmpty(flowCode))
            {
                sql += " AND (m.RealFlowCode IN(" + flowCode + "))";
            }
            sql += " GROUP BY m.AreaCode, m.ClassId";
            return zbdlcontext.Database.SqlQuery<StatisticsModel>(sql).ToList();
        }

        /// <summary>
        /// 专家汇总页面。
        /// </summary>
        /// <param name="classId"></param>
        /// <param name="areaCode"></param>
        /// <returns></returns>
        public List<StatisticsModel2> GetPersonExpertStatistic(int classId, string areaCode, byte? isShenBao)
        {
            List<StatisticsModel2> result = new List<StatisticsModel2>();
            ////P_EF_PsnStatistic
            DataSet dsResult = SqlHelper.ExecuteDataset(
                  (SqlConnection)zbdlcontext.Database.Connection,
                   CommandType.StoredProcedure,
                   "P_EF_PsnStatistic",
                   new SqlParameter[] { 
                    new SqlParameter("@classId",classId),
                    new SqlParameter("@areaCode",areaCode),
                    new SqlParameter("@isShenBao",isShenBao)
                });
            if (dsResult != null && dsResult.Tables.Count > 0)
            {
                foreach (DataRow drItem in dsResult.Tables[0].Rows)
                {
                    int statisticsCount = 0;
                    int.TryParse(drItem["statisticsCount"].ToString(), out statisticsCount);
                    StatisticsModel2 item = new StatisticsModel2()
                    {
                        StatisticsCount = statisticsCount,
                        AreaCode = drItem["Code"].ToString(),
                        AreaCodeTitle = drItem["Name"].ToString(),
                        FlowCode = drItem["FlowCode"].ToString()
                    };
                    result.Add(item);
                }
            }
            return result;
        }

        /// <summary>
        /// 老专家汇总页面。
        /// </summary>
        public DataTable GetOldExpertStatistic(int classId, string areaCode)
        {
            DataSet dsResult = SqlHelper.ExecuteDataset(
                (SqlConnection)zbdlcontext.Database.Connection,
                CommandType.StoredProcedure,
                "P_EF_OldExpertStatistic",
                new SqlParameter[] 
                { 
                    new SqlParameter("@classId",classId),
                    new SqlParameter("@areaCode",areaCode)
                }
            );
            List<StatisticsOldExpert> result = new List<StatisticsOldExpert>();
            if (dsResult != null && dsResult.Tables.Count > 0)
            {
                return dsResult.Tables[0];
            }
            return null;
        }

        /// <summary>
        /// 考试报名统计
        /// </summary>
        /// <param name="qcId"></param>
        /// <param name="areaCode"></param>
        /// <returns></returns>
        public List<StatisticsMode4> GetExpertBMStatistic(int qcId, string areaCode)
        {
            string sql = @"
SELECT m.DBType,COUNT(m.EmpGuid) AS StatisticsCount, COUNT(b.ID) AS StatisticsCount1 
FROM (
    SELECT P.ID, P.CorpUserGuid
    FROM Exam_Info AS EI INNER JOIN tb_PayInfo AS P ON EI.OrderCode = P.Code 
    WHERE (EI.QCId =@qcId) AND (P.bPayStatus = 3) AND (P.IsDel = 0)
) AS b
RIGHT OUTER JOIN V_EF_Manager AS m ON b.CorpUserGuid = m.CorpGuid 
WHERE(m.RealFlowCode LIKE N'1,%') AND (m.DBType IS NOT NULL) 
AND (m.FlowIsDel = 0 OR m.FlowIsDel IS NULL) AND (m.IsDel = 0 OR m.IsDel IS NULL) 
AND m.ClassID=8065";
            if (!string.IsNullOrEmpty(areaCode) && areaCode != "00")
            {
                sql += " AND (m.DBType like '" + areaCode + "%')";
            }
            sql += " GROUP BY m.DBType";
            return zbdlcontext.Database.SqlQuery<StatisticsMode4>(sql, new SqlParameter("@qcId", qcId)).ToList();
        }
        /// <summary>
        /// 专家主专业统计
        /// </summary>
        /// <param name="areaCode"></param>
        /// <returns></returns>
        public List<StatisticsMode3> GetPersonExpertStatistic(string areaCode)
        {
            string sql = "SELECT   COUNT(m.EmpGuid) AS StatisticsCount, m.DBType, LEFT(s.SpecialityCode, 3) AS SpecialityCode FROM  V_EF_Manager AS m INNER JOIN "
                        + "  tb_Expert_Speciality AS s ON m.EmpGuid = s.EmpGuid WHERE   (LEN(s.SpecialityCode) >= 3) AND (s.IsMain = 1) AND (s.IsDel = 0) AND (m.ClassId = 8065) AND (m.FlowAreaCode = N'00') "
                        + "  AND (m.RealFlowCode LIKE N'1,%') AND (m.FlowIsDel = 0 OR m.FlowIsDel IS NULL) AND (m.IsDel = 0 OR m.IsDel IS NULL)";
            if (!string.IsNullOrEmpty(areaCode) && areaCode != "00")
            {
                sql += " AND (m.DBType like '" + areaCode + "%')";
            }
            sql += " GROUP BY m.DBType, LEFT(s.SpecialityCode, 3)";
            return zbdlcontext.Database.SqlQuery<StatisticsMode3>(sql).ToList();
        }

        #region 人员信息 张剑
        /// <summary>
        /// 获取企业下的普通工人信息列表 姜汉鑫改
        /// </summary>
        /// <param name="corpUserGuid">企业Guid</param>
        /// <param name="condition">sql where</param>
        /// <param name="pageIndex">页数</param>
        /// <param name="pageSize">条数</param>
        /// <returns></returns>
        public List<tb_Emp_Manager_Zbdl> GetPersonEmployees(string corpUserGuid, string condition, int pageIndex, int pageSize, out int count)
        {
            var list = new List<tb_Emp_Manager_Zbdl>();

            string sql = "SELECT * FROM tb_Emp_Manager where (isdel=0 or isdel is null) and CorpGuid='" + corpUserGuid + "' " +
            (string.IsNullOrEmpty(condition) ? string.Empty : condition);
            list = zbdlcontext.Database.SqlQuery<tb_Emp_Manager_Zbdl>(sql).OrderByDescending(m => m.ID).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            count = zbdlcontext.Database.SqlQuery<tb_Emp_Manager_Zbdl>(sql).ToList().Count;
            return list;
        }

        /// <summary>
        /// 获取企业下的普通工人信息列表总记录数 姜汉鑫改
        /// </summary>
        /// <param name="corpUserGuid">企业Guid</param>
        /// <param name="condition">sql where</param>
        /// <returns></returns>
        //public int GetPersonEmployeesCount(string corpUserGuid, string condition)
        //{
        //    int count = 0;
        //    string sql = "SELECT count(1) FROM tb_Emp_Manager where (isdel=0 or isdel is null) and  CorpGuid='" + corpUserGuid + "' " +
        //    (string.IsNullOrEmpty(condition) ? string.Empty : condition);
        //    count = zbdlcontext.Database.SqlQuery<int>(sql).FirstOrDefault();
        //    return count;
        //}

        /// <summary>
        /// 获取企业下的指定工人的证书列表
        /// </summary>
        /// <param name="empGuid">empGuid</param>
        /// <param name="condition">sql where</param>
        /// <returns></returns>
        public List<Tb_Emp_TitleDetails_Zbdl> GetPersonTitleDetails(string empGuid, string condition)
        {
            var list = new List<Tb_Emp_TitleDetails_Zbdl>();
            string sql = string.Format("select * from tb_emp_titledetails  where empguid = '{0}' ", empGuid) + (string.IsNullOrEmpty(condition) ? string.Empty : condition);
            list = zbdlcontext.Database.SqlQuery<Tb_Emp_TitleDetails_Zbdl>(sql).ToList();
            return list;
        }

        #endregion

        /// <summary>
        /// 获取项目库押证数量
        /// </summary>
        /// <param name="managerId"></param>
        /// <returns>int</returns>
        public int CheckBzkCounts(int managerId)
        {
            int bzkCounts = 0;
            string sql = String.Format(@"SELECT COUNT(1) FROM  h_ProjectManager WHERE ManagerId={0} 
                                         AND EXISTS (SELECT 1 FROM h_projectBzk_New p INNER JOIN 
                                         h_ProjectCorp c ON p.ID=c.ProjectId 
                                         WHERE c.Id=h_ProjectManager.ProjectCorpId AND p.bStatus<>255 AND p.IsDel=0 AND c.IsDel=0)", managerId);
            bzkCounts = zbdlcontext.Database.SqlQuery<int>(sql).FirstOrDefault();
            return bzkCounts;
        }

        public DataTable GetKaoheRst(string bFlag, string queryStr, string querymember, string outer
            , string zizhi, int pageIndex, int pageSize, out int dataCount)
        {
            string sql = string.Empty;

            sql = "SELECT A.* FROM e_SorcePublish_2006_yt A "
                        + "LEFT JOIN T_SYS_USERS C ON A.TREEID = C.DOGID "
                        + "LEFT JOIN TB_CORP_COMPANYINFO D ON C.USERGUID = D.CORPUSERGUID "
                        + "LEFT JOIN A_CROPKIND E ON A.KINDID = E.ID "
                        + "WHERE 1=1";

            //0本地企业,1外地企业
            if (outer == "1" && (bFlag == "11" || bFlag == "1"))//施工外地企业17
            {
                sql += " and c.bgrade=17 and (d.flowcode like '1%' or d.bcanbid like '%3%') ";
            }
            else
            {
                sql += " and D.TB_CORP_ISOUTER =" + outer + " and c.bGrade<>1500 and D.classID<>250100  and d.flowcode like '1%' ";
            }

            string insertStr = string.Empty;
            if (bFlag == "11" || bFlag == "12")
            {
                insertStr += " and e.isKh=1";   //e.tmpID>0 ";
            }

            //若查询框不是空的
            if (!queryStr.Equals(""))
            {
                insertStr += "and A.corpName like '%" + queryStr + "%'";
            }
            if (!querymember.Equals(""))
            {
                insertStr += " and A.PersonName like '%" + querymember + "%'";
            }
            if (zizhi != "")
            {
                insertStr += " and A.KindID =" + zizhi;
            }
            if (bFlag != "")
            {
                if (bFlag != "43")
                    insertStr += " and A.bFlag  =" + bFlag + " and D.classid <> 2507";
                else
                    insertStr += " and D.classid = 2507";//劳务分包
            }

            insertStr = insertStr + " order by A.Year_Sorce desc, (case A.LevelTitle when '特级' then 1 when '壹级' then 2 when '贰级' then 3 when '叁级' then 4 when '甲级' then 5 when '乙级' then 6 when '丙级' then 7 else 99 end) asc";
            sql += insertStr;

            dataCount = zbdlcontext.Database.SqlQuery<e_SorcePublish_2006_YT>(sql).Count();

            List<e_SorcePublish_2006_YT> list = zbdlcontext.Database.SqlQuery<e_SorcePublish_2006_YT>(sql)
                .Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            DataTable dt = ListToDataTable(list);

            dt.TableName = "DataTable";
            return dt;
        }

        #region  反射List To DataTable
        /// <summary>  
        /// 将集合类转换成DataTable  
        /// </summary>  
        /// <param name="list">集合</param>  
        /// <returns></returns>  
        public static DataTable ListToDataTable(IList list)
        {
            DataTable result = new DataTable();
            if (list.Count > 0)
            {
                PropertyInfo[] propertys = list[0].GetType().GetProperties();

                foreach (PropertyInfo pi in propertys)
                {
                    Type colType = pi.PropertyType;
                    if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                    {
                        colType = colType.GetGenericArguments()[0];
                    }
                    result.Columns.Add(new DataColumn(pi.Name, colType));
                }

                for (int i = 0; i < list.Count; i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in propertys)
                    {
                        object obj = pi.GetValue(list[i], null);
                        tempList.Add(obj);
                    }
                    object[] array = tempList.ToArray();
                    result.LoadDataRow(array, true);
                }
            }
            return result;
        }

        #endregion

        #region 报名考试

        /// <summary>
        /// 获取期次列表
        /// </summary>
        /// <param name="sql">拼接sql</param>
        /// <param name="appId">项目id</param>
        /// <param name="pageIndex">第几页</param>
        /// <param name="pageSize">一页数量</param>
        /// <param name="dataCount">查询数据量</param>
        /// <returns></returns>
        public List<Exam_QC> GetExamQCList(
            string sql,
            string appId,
            int pageIndex,
            int pageSize,
            out int dataCount)
        {
            string select = string.Format("SELECT * FROM Exam_QC WHERE IsDel=0 AND appid=@AppId {0} ORDER BY id DESC ", sql);
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@AppId", appId) };
            SqlParameter[] pars = new SqlParameter[] { new SqlParameter("@AppId", appId) };
            dataCount = zbdlcontext.Database.SqlQuery<Exam_QC>(select, parameters).ToList().Count();

            List<Entity.ZBDLRY.Exam_QC> list = new List<Exam_QC>();
            if (pageIndex == 0)
            {
                ////不分页返回所有数据
                list = zbdlcontext.Database.SqlQuery<Exam_QC>(select, pars).ToList();
            }
            else
            {
                list = zbdlcontext.Database.SqlQuery<Exam_QC>(select, pars).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }
            return list;
        }

        /// <summary>
        /// 获取科目列表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="appId">项目id</param>
        /// <param name="qcId">期次id</param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="dataCount"></param>
        /// <returns></returns>
        public List<Exam_KM> GetExamKMList(
            string sql,
            string appId,
            string qcId,
            int pageIndex,
            int pageSize,
            out int dataCount)
        {
            string select = string.Format("SELECT * FROM Exam_KM a JOIN Exam_QC b on a.QCId=b.ID WHERE a.IsDel=0 AND b.IsDel=0 AND b.AppId=@AppId {0} ", sql);
            List<SqlParameter> parameters = new List<SqlParameter>();
            List<SqlParameter> pars = new List<SqlParameter>();
            parameters.Add(new SqlParameter("@AppId", appId));
            pars.Add(new SqlParameter("@AppId", appId));
            ////查询该期次下的科目
            if (!string.IsNullOrEmpty(qcId))
            {
                select = select + " and a.QCId=@QcId";
                parameters.Add(new SqlParameter("@QcId", qcId));
                pars.Add(new SqlParameter("@QcId", qcId));
            }
            select += " ORDER BY a.QCId desc,a.id desc";

            dataCount = zbdlcontext.Database.SqlQuery<Exam_KM>(select, parameters.ToArray()).Count();
            List<Entity.ZBDLRY.Exam_KM> list = new List<Exam_KM>();
            if (pageIndex == 0)
            {
                list = zbdlcontext.Database.SqlQuery<Exam_KM>(select, pars.ToArray()).ToList();
            }
            else
            {
                list = zbdlcontext.Database.SqlQuery<Exam_KM>(select, pars.ToArray()).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }
            return list;
        }

        /// <summary>
        /// 获取考场列表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="qcId">期次id</param>
        /// <param name="kmId">科目id</param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="dataCount"></param>
        /// <returns></returns>
        public List<Exam_Room> GetExamKCList(
            string sql,
            string appId,
            string qcId,
            string kmId,
            int pageIndex,
            int pageSize,
            out int dataCount)
        {
            string select = string.Format("SELECT Exam_Room.* FROM Exam_Room join Exam_QC on QCId=Exam_QC.ID where Exam_QC.IsDel=0 AND Exam_Room.IsDel=0 AND AppId=@AppId  {0} ", sql);
            List<SqlParameter> parameters = new List<SqlParameter>();
            List<SqlParameter> pars = new List<SqlParameter>();
            parameters.Add(new SqlParameter("@AppId", appId));
            pars.Add(new SqlParameter("@AppId", appId));
            ////查询该期次下的考场
            if (!string.IsNullOrEmpty(qcId))
            {
                select = select + " AND QCId=@QcId ";
                parameters.Add(new SqlParameter("@QcId", qcId));
                pars.Add(new SqlParameter("@QcId", qcId));
            }
            ////获取该科目所在的考场
            if (!string.IsNullOrEmpty(kmId))
            {
                select = select + " AND KMId=@KMId ";
                parameters.Add(new SqlParameter("@KMId", kmId));
                pars.Add(new SqlParameter("@KMId", kmId));
            }
            select += " order by QCId desc,KMId desc,id desc";

            dataCount = zbdlcontext.Database.SqlQuery<Exam_Room>(select, parameters.ToArray()).Count();
            List<Entity.ZBDLRY.Exam_Room> list = new List<Exam_Room>();
            if (pageIndex == 0)
            {
                list = zbdlcontext.Database.SqlQuery<Exam_Room>(select, pars.ToArray()).ToList();
            }
            else
            {
                list = zbdlcontext.Database.SqlQuery<Exam_Room>(select, pars.ToArray()).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }
            return list;
        }

        /// <summary>
        /// 获取考试报名列表
        /// </summary>
        /// <param name="requestType">1:未报名缴费 0:已报名缴费</param>
        public List<ExamInfo> GetExamBMList(
            string sql,
            string appId,
            int qcId,
            string areaCode,
            DTO.tb_Emp_Manager emp,
            int requestType,
            int pageIndex,
            int pageSize,
            out int dataCount)
        {
            string select = String.Empty;
            List<SqlParameter> parameters = new List<SqlParameter>();
            List<SqlParameter> pars = new List<SqlParameter>();
            string corpUserGuid = String.Empty;
            if (emp.EmpGuid != null)
            {
                corpUserGuid = emp.EmpGuid.ToString();
            }

            if (requestType == 1)
            {
                select = string.Format(@"
                    SELECT
	                    0 AS ID,ISNULL(Exam_QC.ID,0) AS QCID,tb_Emp_Manager.DBType AS AreaCode,tb_Emp_Manager.EmpGuid AS CorpUserGuid
	                    ,Exam_QC.Title,'' AS OrderCode,0 EmpCount,
	                    NULL AS BMDate, Exam_QC.EndDate,Exam_QC.StartDate,Exam_QC.ExamDate,tb_Emp_Manager.Name AS CorpName,T_SYS_DICT.Name as Area
                    FROM (
	                    SELECT
		                    tb_Emp_Manager.*,Exam_QC.ID QCID
	                    FROM tb_Emp_Manager,Exam_QC
	                    WHERE tb_Emp_Manager.ClassID = 8065 AND Exam_QC.IsDel = 0
	                    AND tb_Emp_Manager.DBType LIKE '{0}%'
	                    AND Exam_QC.AppID = @AppId
                    )tb_Emp_Manager
                    LEFT JOIN T_SYS_DICT ON T_SYS_DICT.Code = tb_Emp_Manager.DBType
                    LEFT JOIN tb_FlowCode ON CAST(tb_Emp_Manager.EmpGuid AS NVARCHAR(50))=tb_FlowCode.ObjectID
                    AND tb_Emp_Manager.ClassId=tb_FlowCode.CodeType
                    LEFT JOIN Exam_QC ON Exam_QC.ID =tb_Emp_Manager.QCId
                    LEFT JOIN Exam_Info ON Exam_Info.CorpUserGuid = tb_Emp_Manager.EmpGuid
                    AND Exam_Info.QCID = Exam_QC.ID AND Exam_Info.IsDel = 0
                    LEFT JOIN tb_PayInfo ON tb_PayInfo.Code = Exam_Info.OrderCode AND tb_PayInfo.IsDel = 0
                    WHERE tb_FlowCode.FlowCode LIKE '1,%'
                    AND (tb_PayInfo.bPayStatus != 3 OR tb_PayInfo.bPayStatus IS NULL)", areaCode ?? "");

                if (!String.IsNullOrEmpty(emp.Name))
                {
                    select = select + " AND tb_Emp_Manager.Name LIKE @EmpName";
                    parameters.Add(new SqlParameter("@EmpName", "%" + emp.Name + "%"));
                    pars.Add(new SqlParameter("@EmpName", "%" + emp.Name + "%"));
                }
                if (!String.IsNullOrEmpty(emp.IDCard))
                {
                    select = select + " AND tb_Emp_Manager.IDCard = @IDCard";
                    parameters.Add(new SqlParameter("@IDCard", emp.IDCard));
                    pars.Add(new SqlParameter("@IDCard", emp.IDCard));
                }
            }
            else
            {
                select = string.Format(@"
                    SELECT
	                    Exam_Info.ID,Exam_QC.ID AS QCID,Exam_Info.AreaCode,Exam_Info.CorpUserGuid,
                        Exam_QC.Title,Exam_Info.OrderCode,Exam_Info.BMDate, Exam_QC.EndDate,Exam_QC.StartDate,
	                    (
		                    SELECT COUNT(*)
		                    FROM Exam_SubInfo SI
                            INNER JOIN Exam_Info EI ON SI.TreeId=EI.ID
		                    WHERE EI.QCID=Exam_QC.ID AND SI.IsDel=0 AND EI.IsDel=0
	                    ) EmpCount,
	                    Exam_QC.ExamDate,Exam_Info.CorpName,T_SYS_DICT.Name as Area,ZKZPrintTime
                    FROM Exam_QC
                    INNER JOIN Exam_Info ON Exam_QC.ID =Exam_Info.QCId 
                    INNER JOIN tb_PayInfo ON tb_PayInfo.Code = Exam_Info.OrderCode
                    INNER JOIN T_SYS_DICT ON T_SYS_DICT.Code = Exam_Info.AreaCode
                    INNER JOIN tb_Emp_Manager ON Exam_Info.CorpUserGuid = tb_Emp_Manager.EmpGuid
                    WHERE Exam_QC.IsDel=0 AND Exam_Info.IsDel=0 AND tb_PayInfo.bPayStatus=3
                    AND Exam_Info.CorpUserGuid IS NOT NULL AND T_SYS_DICT.Code LIKE '{0}%'
                    AND Exam_QC.AppID =@AppId ", areaCode ?? "");

                if (sql.Contains("@CorpUserGuid"))
                {
                    select = select + " AND Exam_Info.CorpUserGuid=@CorpUserGuid";
                    parameters.Add(new SqlParameter("@CorpUserGuid", corpUserGuid));
                    pars.Add(new SqlParameter("@CorpUserGuid", corpUserGuid));
                }
                if (!String.IsNullOrEmpty(emp.Name))
                {
                    select = select + " AND Exam_Info.CorpName LIKE @EmpName";
                    parameters.Add(new SqlParameter("@EmpName", "%" + emp.Name + "%"));
                    pars.Add(new SqlParameter("@EmpName", "%" + emp.Name + "%"));
                }
                if (!String.IsNullOrEmpty(emp.IDCard))
                {
                    select = select + " AND tb_Emp_Manager.IDCard = @IDCard";
                    parameters.Add(new SqlParameter("@IDCard", emp.IDCard));
                    pars.Add(new SqlParameter("@IDCard", emp.IDCard));
                }
            }

            parameters.Add(new SqlParameter("@AppId", appId));
            pars.Add(new SqlParameter("@AppId", appId));

            ////获取某个地区的报名信息
            if (String.IsNullOrEmpty(areaCode) || areaCode == "00")
            {
                areaCode = "";
            }
            parameters.Add(new SqlParameter("@AreaCode", areaCode));
            pars.Add(new SqlParameter("@AreaCode", areaCode));

            if (qcId != 0)
            {
                select = select + " AND Exam_QC.ID=@QcId";
                parameters.Add(new SqlParameter("@QcId", qcId));
                pars.Add(new SqlParameter("@QcId", qcId));
            }
            select = select + " ORDER BY Exam_QC.ID DESC,T_SYS_DICT.SortValue,Exam_Info.BMDate";

            dataCount = zbdlcontext.Database.SqlQuery<ExamInfo>(select, parameters.ToArray()).Count();
            List<ExamInfo> list = zbdlcontext.Database.SqlQuery<ExamInfo>(select, pars.ToArray()).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            return list;
        }

        /// <summary>
        /// 获取考场座次
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="appId"></param>
        /// <param name="kcId"></param>
        /// <param name="corpUserGuid"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="dataCount"></param>
        /// <returns></returns>
        public List<ExamEmpRoom> GetKCZCList(
            string sql,
            string appId,
            int kcId,
            string corpUserGuid,
            int pageIndex,
            int pageSize,
            out int dataCount)
        {
            string select = string.Format(@"SELECT a.ID,q.Title as QCTitle,b.QCId,m.Title as KMTitle,b.KMId,b.Title as KCTitle,a.KCId,a.ExamSubId,a.ExamCertCode,a.SeatCode
             ,c.EmpGuid,c.EmpName,c.IDCard,b.ExamDate,b.ExamAddress,m.ExamLength
             from Exam_EmpRoom a 
             JOIN Exam_Room b ON a.KCId=b.ID
             JOIN Exam_KM m ON b.KMId=m.ID
             JOIN Exam_SubInfo c ON a.ExamSubId=c.ID
             JOIN Exam_Info d ON c.TreeId=d.ID 
             JOIN Exam_QC q ON d.QCId=q.ID and b.QCId=q.ID and m.QCId=q.ID
             WHERE a.IsDel=0 AND b.IsDel=0 AND m.IsDel=0  AND c.IsDel=0 AND d.IsDel=0 AND q.IsDel=0 and q.AppId =@AppId {0} ", sql);

            select = select + " ORDER BY a.KCId, a.SeatCode ";
            List<SqlParameter> parameters = new List<SqlParameter>();
            List<SqlParameter> pars = new List<SqlParameter>();
            parameters.Add(new SqlParameter("@AppId", appId));
            pars.Add(new SqlParameter("@AppId", appId));
            if (select.Contains("@CorpUserGuid"))
            {
                parameters.Add(new SqlParameter("@CorpUserGuid", corpUserGuid));
                pars.Add(new SqlParameter("@CorpUserGuid", corpUserGuid));
            }
            if (select.Contains("@KcId"))
            {
                parameters.Add(new SqlParameter("@KcId", kcId));
                pars.Add(new SqlParameter("@KcId", kcId));
            }
            dataCount = zbdlcontext.Database.SqlQuery<ExamEmpRoom>(select, parameters.ToArray()).Count();
            List<ExamEmpRoom> list = new List<ExamEmpRoom>();
            if (pageIndex == 0)
            {
                list = zbdlcontext.Database.SqlQuery<ExamEmpRoom>(select, pars.ToArray()).ToList();
            }
            else
            {

                list = zbdlcontext.Database.SqlQuery<ExamEmpRoom>(select, pars.ToArray()).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }
            return list;
        }

        /// <summary>
        /// 报名统计
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="appId"></param>
        /// <param name="qcId"></param>
        /// <returns></returns>
        public DataTable BMStatistics(string sql, string appId, int qcId, string areaCode)
        {
            string select = string.Format(@"
SELECT a_AreaInfo.Code,a_AreaInfo.Title,(select count(*) from Exam_Info join Exam_QC on QCId=Exam_QC.ID
     where LEFT(Exam_Info.AreaCode,2)=a_AreaInfo.Code 
and Exam_Info.IsDel=0 AND Exam_QC.IsDel=0 AND AppId=@AppId {0}) CorpCount from  a_AreaInfo where a_AreaInfo.Code<>'00' ", "{0}");
            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("@AppId", appId));
            if (qcId != 0)
            {
                select = string.Format(select, sql);
                parameters.Add(new SqlParameter("@QcId", qcId));
            }
            if (!String.IsNullOrEmpty(areaCode))
            {
                select += String.Format(" AND a_AreaInfo.Code = '{0}'", areaCode);
            }
            var list = zbdlcontext.Database.SqlQuery<Exam_Statistics>(select, parameters.ToArray()).ToList();
            DataTable dt = ListToDataTable(list);
            dt.TableName = "DataTable";
            return dt;
        }

        /// <summary>
        /// 成绩公布列表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="corpUserGuid"></param>
        /// <param name="appId"></param>
        /// <param name="qcId"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="dataCount"></param>
        /// <returns></returns>
        public DataTable GetScoreList(
            string sql,
            string corpUserGuid,
            string appId,
            int qcId,
            int pageIndex,
            int pageSize,
            out int dataCount)
        {
            string select = string.Format(@" SELECT c.Title,b.CorpName,a.EmpName,a.IDCard,a.ExamCertCode,d.Title as KMTitle,ISNULL(e.Score,0) as Score 
into #temp
FROM Exam_SubInfo a 
JOIN Exam_Info b ON a.TreeId=b.ID
JOIN Exam_QC c ON b.QCId=c.ID
JOIN Exam_KM d ON d.QCId=c.ID
LEFT JOIN Exam_Score e ON e.KMId=d.ID and (e.ExamSubId=a.ID OR e.ExamSubId IS NULL)
WHERE a.IsDel=0 and b.IsDel=0 and c.IsDel=0 and d.IsDel=0 AND  c.ID=@QcId and  c.AppId=@AppId {0} 
ORDER BY c.ID,b.CorpUserGuid,a.ID,d.ID

declare @sql varchar(500)
set @sql='select Title 期次,CorpName 单位名称,EmpName 姓名,IDCard 身份证号,ExamCertCode 准考证号'
select @sql=@sql+',max(case KMTitle when '''+KMTitle+''' then Score else 0 end)['+KMTitle+']'
from(select distinct KMTitle from #temp)a
set @sql=@sql+' from #temp group by Title,CorpName,EmpName,IDCard,ExamCertCode order by CorpName'
exec(@sql)
drop table #temp", sql);
            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("@AppId", appId));
            parameters.Add(new SqlParameter("@QcId", qcId));
            if (select.Contains("@CorpUserGuid"))
            {
                parameters.Add(new SqlParameter("@CorpUserGuid", corpUserGuid));
            }
            DataTable dt = new CommonHelper().GetTable(select, parameters.ToArray(), (pageIndex - 1) * pageSize, pageIndex * pageSize, "DataTable", out dataCount);
            return dt;
        }

        /// <summary>
        /// 获取准考证列表
        /// </summary>
        /// <param name="sql">拼接语句</param>
        /// <param name="appId">项目id</param>
        /// <param name="qcId">期次id</param>
        /// <param name="areaCode">地区号</param>
        /// <param name="corpName">单位名称</param>
        /// <param name="corpUserGuid">单位guid</param>
        /// <param name="empName">人员名称</param>
        ///<param name="ids">报名子表id集合</param>
        /// <param name="pageIndex">当前页数</param>
        /// <param name="pageSize">一页数量</param>
        /// <param name="dataCount">结果集数量</param>
        /// <returns>准考证集合</returns>
        public List<Exam_ZKZ> GetZKZList(
            string sql,
            string appId,
            int qcId,
            string areaCode,
            string corpName,
            string corpUserGuid,
            string empName,
            string ids,
            int pageIndex,
            int pageSize,
            out int dataCount)
        {
            string select = string.Format(@"
SELECT
    Exam_SubInfo.ID, Exam_QC.ID as QCID,Exam_QC.Title as QCTitle,Exam_Info.AreaCode,T_Sys_Dict.Name as Area,
    Exam_Info.CorpUserGuid,CC.CorpName,EmpName,EmpGuid,IDCard,ExamCertCode
FROM  Exam_Info
INNER JOIN Tb_PayInfo ON Exam_Info.OrderCode=Tb_PayInfo.Code AND Tb_PayInfo.bPayStatus=3
INNER JOIN Exam_QC ON Exam_QC.ID=Exam_Info.QCId
INNER JOIN Exam_SubInfo on Exam_Info.ID=TreeId
LEFT JOIN T_Sys_Dict on T_Sys_Dict.ROOTCODE='EXPERTDBCODE' AND Exam_Info.AreaCode=T_Sys_Dict.Code
LEFT JOIN TB_Corp_Companyinfo CC ON CC.CorpUserGuid=Exam_Info.CorpUserGuid
WHERE  Exam_QC.IsDel=0 AND Exam_Info.IsDel=0  AND Exam_SubInfo.IsDel=0
AND Exam_QC.AppID  =@AppId {0} ", sql);
            List<SqlParameter> parameters = new List<SqlParameter>();
            List<SqlParameter> pars = new List<SqlParameter>();
            parameters.Add(new SqlParameter("@AppId", appId));
            pars.Add(new SqlParameter("@AppId", appId));
            ////按照期次查询
            if (qcId != 0)
            {
                select = select + " and Exam_QC.ID=@QcId";
                parameters.Add(new SqlParameter("@QcId", qcId));
                pars.Add(new SqlParameter("@QcId", qcId));
            }
            ////按照所在单位名称查询
            if (!string.IsNullOrEmpty(corpName))
            {
                select = select + " AND CC.CorpName like @CorpName";
                parameters.Add(new SqlParameter("@CorpName", "%" + corpName + "%"));
                pars.Add(new SqlParameter("@CorpName", "%" + corpName + "%"));
            }
            ////按照人员名称查询
            if (!string.IsNullOrEmpty(empName))
            {
                select = select + " and EmpName like @EmpName";
                parameters.Add(new SqlParameter("@EmpName", "%" + empName + "%"));
                pars.Add(new SqlParameter("@EmpName", "%" + empName + "%"));
            }
            ////按照地区查询
            if (!string.IsNullOrEmpty(areaCode))
            {
                select = select + " and T_Sys_Dict.Code=@AreaCode";
                parameters.Add(new SqlParameter("@AreaCode", areaCode));
                pars.Add(new SqlParameter("@AreaCode", areaCode));
            }
            ////企业端显示该企业下信息
            if (select.Contains("@CorpUserGuid"))
            {
                parameters.Add(new SqlParameter("@CorpUserGuid", corpUserGuid));
                pars.Add(new SqlParameter("@CorpUserGuid", corpUserGuid));
            }
            ////获取指定人员信息
            if (!string.IsNullOrEmpty(ids))
            {
                ////接收的id集合
                string[] strs = ids.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
                int[] ints = Array.ConvertAll<string, int>(strs, i => int.Parse(i));
                select = string.Format(select + " and Exam_SubInfo.ID in ({0})", string.Join(",", ids.Trim(',')));
            }
            select = select + " ORDER BY Exam_QC.ID DESC,T_Sys_Dict.SortValue,Exam_Info.ID";

            dataCount = zbdlcontext.Database.SqlQuery<Exam_ZKZ>(select, parameters.ToArray()).Count();
            List<Exam_ZKZ> list = new List<Exam_ZKZ>();
            if (pageSize == 0)
            {
                list = zbdlcontext.Database.SqlQuery<Exam_ZKZ>(select, pars.ToArray()).ToList();
            }
            else
            {
                list = zbdlcontext.Database.SqlQuery<Exam_ZKZ>(select, pars.ToArray()).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }
            return list;
        }


        public string GetExpertExamType(Guid userGuid)
        {
            string result = String.Empty;
            string sql = String.Format(@"
                --初始申请人员
                SELECT '1' FROM tb_emp_manager EM
                INNER JOIN tb_flowcode F ON CAST(EM.empguid AS NVARCHAR(50))=F.objectid 
                AND EM.ClassId=F.CodeType
                WHERE F.FlowCode LIKE '1,%' AND (EM.ExpertCardID IS NULL OR EM.ExpertCardID = '')
                AND EM.EmpGuid = '{0}'", userGuid.ToString());

            if (zbdlcontext.Database.SqlQuery<string>(sql).Count() > 0)
            {
                result = "1";
            }
            else
            {
                sql = String.Format(@"
                --资格复审人员
                SELECT 2 FROM tb_emp_manager EM
                INNER JOIN tb_flowcode F ON CAST(EM.empguid AS NVARCHAR(50))=F.objectid 
                AND EM.ClassId=F.CodeType
                WHERE F.FlowCode LIKE '1,%' AND (EM.ExpertCardID IS NOT NULL AND EM.ExpertCardID != '')
                AND DATEDIFF(DAY,EM.ExpertCardEndDate,GETDATE()) < 30*3
                AND EM.EmpGuid = '{0}'", userGuid.ToString());

                if (zbdlcontext.Database.SqlQuery<string>(sql).Count() > 0)
                {
                    result = "2";
                }
            }
            return result;
        }

        /// <summary>
        /// 获取人员准考证详细信息
        /// </summary>
        /// <param name="subId">sub表id</param>
        /// <returns>考场及科目列表</returns>
        public List<ExamEmpRoom> GetKCZCList(int subId)
        {
            string select = string.Format(@"SELECT a.ID,a.QCId,b.Title as QCTitle,d.ID as KMId,d.Title as KMTitle,isnull(e.ID,null) KCId,e.Code KCTitle,
c.ID ExamSubId,c.ExamCertCode,f.SeatCode,c.EmpGuid,c.EmpName,c.IDCard,e.ExamDate,e.ExamAddress,d.ExamLength
FROM  Exam_Info a
join Exam_QC b ON a.QCId=b.id
JOIN Exam_SubInfo c on a.ID=c.TreeId
JOIN Exam_KM d on d.QCId=b.ID 
left JOIN Exam_EmpRoom f ON f.ExamSubId=c.ID AND f.kmid=d.id 
LEFT join Exam_Room e ON f.KCId=e.ID 
WHERE a.IsDel=0 AND b.IsDel=0 AND c.IsDel=0 AND d.IsDel=0 AND (e.IsDel=0 OR e.IsDel IS NULL ) AND (f.IsDel=0 OR f.IsDel IS NULL )and c.ID={0} ", subId);
            return zbdlcontext.Database.SqlQuery<ExamEmpRoom>(select).ToList();
        }
        #endregion


        /// <summary>
        /// 获取项目(在建&投标中)信息
        /// </summary>
        /// <param name="empID">用户身份证号</param>
        public List<ProjectInfo> GetZtbManagerProjectInfo(string idCard)
        {
            var rst = new List<Entity.ProjectInfo>();

            if (String.IsNullOrEmpty(idCard))
            {
                return rst;
            }
            SqlParameter[] sps = new SqlParameter[]
            {
                new SqlParameter("idCard",idCard)
            };

            string sql = "EXEC P_ZTB_GetXmfzrProjectInfo @idCard";
            var query = zbdlcontext.Database.SqlQuery<Entity.ProjectInfo>(sql, sps);
            rst = query.ToList();
            return rst;
        }
        
        #region 专家相关

        /// <summary>
        /// 根据专家证号获取广联达老数据中的专家信息
        /// </summary>
        /// <param name="expertCode">专家证号</param>
        /// <returns></returns>
        public tb_Emp_ExpertGLD GetExpertGLDInfo(string tel)
        {
            string select = string.Format("SELECT * FROM tb_Emp_ExpertGLD WHERE Tel=@Tel");
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@Tel", tel) };
            tb_Emp_ExpertGLD exp = zbdlcontext.Database.SqlQuery<tb_Emp_ExpertGLD>(select, parameters).FirstOrDefault();
            return exp;
        }

        /// <summary>
        /// 未上报老专家列表
        /// </summary>
        /// <param name="areaCode">库别</param>
        /// <param name="name">姓名</param>
        public DataTable GetNonPostExperts(string areaCode, string name)
        {
            string sql = @"select * from(
select e.Name,e.CorpName,e.IDCard,e.MobileTel tel,e.DBType areacode,1 type 
from tb_emp_manager e
inner join Tb_FlowCode f on cast(e.EmpGuid as nvarchar(50))=f.ObjectId
where e.isShenBao=3
union
select g.Name,g.CorpName,g.IDCard,g.Tel,g.AreaCode,0 type
from Tb_Emp_ExpertGLD g
where not exists(select 1 from tb_emp_manager where g.ExpertCode=ExpertCardID and ClassId=8065)
) a where 1=1 ";
            if (!string.IsNullOrEmpty(areaCode))
            {
                sql += " and areacode like @areacode";
            }
            if (!string.IsNullOrEmpty(name))
            {
                sql += " and name like @name";
            }
            DataSet ds = SqlHelper.ExecuteDataset(CommandType.Text, sql,
                new SqlParameter[]{ 
                    new SqlParameter("areacode", areaCode + "%"),
                    new SqlParameter("name", "%"+name + "%")
            });
            if (ds != null && ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            return null;
        }

        public DataTable GetOldExpertList(out int count, int pageNum, int pageSize, string flowType
            , string areaCode,string name,string auditType)
        {
            DataSet dsResult = SqlHelper.ExecuteDataset(
                (SqlConnection)zbdlcontext.Database.Connection,
                CommandType.StoredProcedure,
                "P_EF_OldExpertList",
                new SqlParameter[] 
                { 
                    new SqlParameter("@FlowType",flowType)
                    ,new SqlParameter("@AreaCode",areaCode)
                    ,new SqlParameter("@Name",name)
                    ,new SqlParameter("@AuditType",auditType)
                    ,new SqlParameter("@PageIndex",pageNum)
                    ,new SqlParameter("@PageSize",pageSize)
                }
            );
            List<StatisticsOldExpert> result = new List<StatisticsOldExpert>();
            if (dsResult != null && dsResult.Tables.Count == 2)
            {
                int.TryParse(dsResult.Tables[0].Rows[0][0].ToString(), out count);
                return dsResult.Tables[1];
            }
            else
            {
                count = 0;
                return null;
            }
        }

        #endregion
        #region 获取人员信息列表 zhjj
        public List<DTO.tb_Emp_Manager> GetCorpEmpList(string CorpUserGuid, string conditions, int pageCount, int pageSize, out int count)
        {
            StringBuilder strsql = new StringBuilder();
            strsql.Append("select m.id, m.EmpGuid, m.Name,m.isFreeze,t.CertificateCode,t.CertificateName from tb_emp_manager m ");
            strsql.Append(" LEFT JOIN tb_Emp_TitleDetails t ON m.EmpGuid=t.EmpGuid");
            strsql.Append(" join tb_flowcode f  on cast(m.empguid as nvarchar(50))=f.objectid");
            strsql.Append(" where m.corpguid=@corpGuid and m.isdel=0");
            //  strsql.append(" and f.flowcode like '1%'");

            SqlParameter[] parameter ={
                new SqlParameter("@corpGuid","'"+CorpUserGuid+"'" )  
                                      };

            List<DTO.tb_Emp_Manager> list = zbdlcontext.Database.SqlQuery<DTO.tb_Emp_Manager>(strsql.ToString(), parameter).ToList();
            count = list.Count;
            return list;
        }
        #endregion
    }

    public static class DataTableExtensions
    {
        /// <summary>
        /// 转换为一个DataTable
        /// </summary>
        /// <typeparam name="TResult"></typeparam>
        /// <param name="value"></param>
        /// <returns></returns>
        public static DataTable ToDataTable<TResult>(this IEnumerable<TResult> value) where TResult : class
        {
            if (value == null || value.Count() == 0)
                return new DataTable();

            //创建属性的集合
            List<System.Reflection.PropertyInfo> pList = new List<System.Reflection.PropertyInfo>();
            //获得反射的入口
            Type type = typeof(TResult);
            DataTable dt = new DataTable();
            //把所有的public属性加入到集合 并添加DataTable的列
            Array.ForEach<System.Reflection.PropertyInfo>(type.GetProperties(), p => { pList.Add(p); dt.Columns.Add(p.Name, p.PropertyType); });
            foreach (var item in value)
            {
                //创建一个DataRow实例
                DataRow row = dt.NewRow();
                //给row 赋值
                pList.ForEach(p => row[p.Name] = p.GetValue(item, null));
                //加入到DataTable
                dt.Rows.Add(row);
            }
            return dt;
        }
    }
}
